########################################################################
#  3account - personal finance data-base
#  Copyright (C) 2007,8,9 Ehud Ben-Reuven
#  udi@benreuven.com
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License
# as published by the Free Software Foundation version 2.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA
########################################################################
"""Convert BLL investment activity XLS file to flat-OFX-RDF and
load into Sesame server.
"""
from csv2flt import *
import re
from xls2csv2 import xls2csv2
from n3tagvalue import *
import logging

fltparams=None

def acctidre(name):
    return '(?P<%s>[\d\-\/]+)'%name

def secid(name):
    return '(?P<%s>\d{6,7})'%name

htf_list=[
#version 0
([
    ['', '', '', '', '', '', '', '', '', '', ''],
    ['\xfa\xf0\xe5\xf2\xe5\xfa \xe4\xf1\xe8\xe5\xf8\xe9\xe5\xfa', '', '', '',
     '', '', '', '', '', '', ''], # In Hebrew: Historical transactions
    ['\xfa\xe0\xf8\xe9\xea: '+eurdre('DTASOF','/'), '', '', '', '', '', '', '',
     '', '', ''],
    ['', '', '', '', '', '', '', '', '', '', ''],
    ['', '', '', '', '', '', '', '', '', '', ''],
    ['\xee\xf1\xf4\xf8 \xf0\xe9\xe9\xf8', # Asset #
     '\xf9\xed \xe4\xf0\xe9\xf8', # Asset Name
     '\xf4\xf2\xe5\xec\xe4', # operation
     '\xfa\xe0\xf8\xe9\xea \xfa\xf9\xec\xe5\xed', # date of payment
     '\xeb\xee\xe5\xfa \xee\xf7\xe5\xf8\xe9\xfa', #original amount
     '\xeb\xee\xe5\xfa \xe1\xe9\xf6\xe5\xf2', # actual amount
     '\xf9\xf2\xf8 \xe1\xe9\xf6\xe5\xf2', # rate
     '\xfa\xee\xe5\xf8\xe4', # received
     '\xf2\xee\xec\xe5\xfa', # expense
     '\xee\xe8\xe1\xf2', # currency
     '\xf1\xe8\xe8\xe5\xf1'] # status
],
[secid('SECID')+'.0',
 strre('SECNAME'),
 strre('TRNTYPE'),
 eurdre('DTSETTLE','/',year_len=2)+'( 00:00:00)?',
 floatre('ORIGUNITS'),
 floatre('UNITS'),
 floatre('UNITPRICE'),
 floatre('net_total'),
 floatre('FEES'),
 strre('CURRENCY'),
 strre('STATUS')],
None),
#version1
([
    ['', '', '', '', '', '', '', '', '', ''],
    ['\xfa\xf0\xe5\xf2\xe5\xfa \xe4\xf1\xe8\xe5\xf8\xe9\xe5\xfa', '', '', '', 
     '', '', '', '', '', ''], # In Hebrew: Historical transactions
    ['('+'\xfa\xe0\xf8\xe9\xea: '+eurdre('DTASOF','/')+')?', '', '', '', '', '',
     '', '', '('+'\xfa\xe0\xf8\xe9\xea: '+eurdre('DTASOF','/')+')?', ''],
    ['', '', '', '', '', '', '', '', '', ''],
    ['', '', '', '', '', '', '', '', '', ''],
    ['\xee\xf1\xf4\xf8 \xf0\xe9\xe9\xf8', # Asset #
     '\xf9\xed \xe4\xf0\xe9\xf8', # Asset Name
     '\xf4\xf2\xe5\xec\xe4', # operation
     '\xfa\xe0\xf8\xe9\xea \xf2\xf8\xea', # value date
     '\xeb\xee\xe5\xfa \xee\xf7\xe5\xf8\xe9\xfa', #original amount
     '\xeb\xee\xe5\xfa \xe1\xe9\xf6\xe5\xf2', # actual amount
     '\xf9\xf2\xf8 \xe1\xe9\xf6\xe5\xf2', # rate
     '\xfa\xee\xe5\xf8\xe4 \xf0\xe8\xe5', # received net
     '\xf2\xee\xec\xe5\xfa', # expense
     '\xf1\xe8\xe8\xe5\xf1'] # status
],
[secid('SECID')+'.0',
 strre('SECNAME'),
 strre('TRNTYPE'),
 eurdre('DTSETTLE','/',year_len=2)+'( 00:00:00)?',
 floatre('ORIGUNITS'),
 floatre('UNITS'),
 floatre('UNITPRICE'),
 floatre('net_total'),
 floatre('FEES'),
 strre('STATUS')],
None),
#version2
([
    ['', '', '', '', '', '', '', '', '', '', ''],
    ['\xfa\xf0\xe5\xf2\xe5\xfa \xe4\xf1\xe8\xe5\xf8\xe9\xe5\xfa', '', '', '',
     '', '', '', '', '', '', ''], # In Hebrew: Historical transactions
    ['('+'\xfa\xe0\xf8\xe9\xea: '+eurdre('DTASOF','/')+')?', '', '', '', '', '',
     '', '', '', '', ''],
    ['', '', '', '', '', '', '', '', '', '', ''],
    ['', '', '', '', '', '', '', '', '', '', ''],
    ['\xee\xf1\xf4\xf8 \xf0\xe9\xe9\xf8', # Asset #
     '\xf9\xed \xe4\xf0\xe9\xf8', # Asset Name
     '\xf4\xf2\xe5\xec\xe4', # operation
     '\xfa\xe0\xf8\xe9\xea \xf2\xf8\xea', # value date
     '\xeb\xee\xe5\xfa \xee\xf7\xe5\xf8\xe9\xfa', #original amount
     '\xeb\xee\xe5\xfa \xe1\xe9\xf6\xe5\xf2', # actual amount
     '\xf9\xf2\xf8 \xe1\xe9\xf6\xe5\xf2', # rate
     '\xfa\xee\xe5\xf8\xe4 \xf0\xe8\xe5', # received net
     '\xf2\xee\xec\xe5\xfa', # expense
     '\xee\xe8\xe1\xf2', # currency
     '\xf1\xe8\xe8\xe5\xf1'] # status
],
[secid('SECID')+'.0',
 strre('SECNAME'),
 strre('TRNTYPE'),
 eurdre('DTSETTLE','/',year_len=2)+'( 00:00:00)?',
 floatre('ORIGUNITS'),
 floatre('UNITS'),
 floatre('UNITPRICE'),
 floatre('net_total'),
 floatre('FEES'),
 strre('CURRENCY'),
 strre('STATUS')],
None),
#version 3
([
    ['', '', '', '', '', '', '', '', ''],
    ['\xfa\xf0\xe5\xf2\xe5\xfa \xe4\xf1\xe8\xe5\xf8\xe9\xe5\xfa', '', '', '',
     '', '', '', '', ''], # In Hebrew: Historical transactions
    ['('+'\xfa\xe0\xf8\xe9\xea: '+eurdre('DTASOF','/')+')?', '', '', '', '', '',
     '', '', ''],
    ['', '', '', '', '', '', '', '', ''],
    ['', '', '', '', '', '', '', '', ''],
    ['\xee\xf1\xf4\xf8 \xf0\xe9\xe9\xf8', # Asset #
     '\xf9\xed \xe4\xf0\xe9\xf8', # Asset Name
     '\xf4\xf2\xe5\xec\xe4', # operation
     '\xfa\xe0\xf8\xe9\xea \xfa\xf9\xec\xe5\xed', # payment date
     '\xeb\xee\xe5\xfa', #amount
     '\xf9\xf2\xf8', # rate
     '\xfa\xee\xe5\xf8\xe4', # received
     '\xf2\xee\xec\xe5\xfa', # expense
     '\xee\xe8\xe1\xf2', # currency
     ] # status
],
[secid('SECID')+'.0',
 strre('SECNAME'),
 strre('TRNTYPE'),
 eurdre('DTSETTLE','/',year_len=2)+'( 00:00:00)?',
 floatre('UNITS'),
 floatre('UNITPRICE'),
 floatre('net_total'),
 floatre('FEES'),
 strre('CURRENCY'),
 ],
None)
]

def pre(fname,csvfile,hashing=False):
    xls2csv2(fname,csvfile,'hebrew')

def flat(csvfile,fout,context=None,hashing=False):
    n3header(fout,"$Id$"[1:-1],context)

    h,t,f,version=readcsvtable_multiformat(csvfile,htf_list)

    l=h[2]
    dtasof = l['DTASOF_year']+l['DTASOF_month']+l['DTASOF_day']
    accttype='INVESTMENT'

    for l in t:
        print >>fout,"a3:flatLine [ ",
        dtsettle = l['DTSETTLE_year']+l['DTSETTLE_month']+l['DTSETTLE_day']
        if dtsettle>dtasof:
            raise Exception('DTSETTLE=%s after DTASOF=%s of the entire document'
                            %(dtsettle,dtasof))
        units=float(l['UNITS'])
        origunits=float(l['ORIGUNITS']) if 'ORIGUNITS' in l else None
        if units:
            unitprice=float(l['UNITPRICE'])/100
            assert unitprice>0
        else:
            unitprice=0
            assert float(l['UNITPRICE'])==0

        taxes=0
        interest=0
        cost=0
        total=0
        net_total=0
        fees=0

        fees=float(l['FEES'])
        if version==1 or version==2: fees=-fees
        wrong_fee_sign= fees<0
        if wrong_fee_sign:
            logging.warning('%s','Wrong fees sign %f'%fees)
            fees=-fees


        try:
            trntype={'\xf7\xf0\xe9\xe4':'BUY',
                     '\xee\xeb\xe9\xf8\xe4':'SELL',
                     '\xe4\xe6\xee\xf0\xe4':'ORDER',
                     '\xf8\xe9\xe1\xe9\xfa':'INT',
                     '\xe2\xe1\xe9\xfa \xee\xf1':'TAX',
                     '\xe4\xe7\xe6\xf8 \xee\xf1 \xf8\xe5\xe5\xe7 \xe4\xe5\xef':
                     'TAXRETURN',
                     '\xe3\xe9\xe1\xe9\xe3\xf0\xe3':'DIV',
                     }[l['TRNTYPE']]
        except:
            logging.error('Unknown transaction type %s'%l['TRNTYPE'])
            raise Exception('Unknown transaction type %s'%l['TRNTYPE'])
        if trntype=='ORDER':
            if units>0:
                trntype='BUY'
            else:
                trntype='SELL'
        price=float(l['net_total'])
        if trntype=='BUY':
            if price>=0:
                if wrong_fee_sign:
                    logging.warning('%s','Wrong BUY sign')
                else:
                    print 'Wrong BUY sign'
                price=-price
        elif trntype=='SELL':
            if price<=0:
                if wrong_fee_sign:
                    logging.warning('%s','Wrong SELL sign')
                else:
                    print 'Wrong SELL sign'
                price=-price
        elif trntype=='INT':
            if price<=0:
                if wrong_fee_sign:
                    logging.warning('%s','Wrong INT sign')
                else:
                    print 'Wrong INT sign'
                price=-price

        if version==1 or version==2:
            total=price
            net_total=total+fees
        else:
            net_total=price
            total=net_total-fees

        if version==1:
            currency='ILS'
        else:
            try:
                currency={'\xf9"\xe7':'ILS'}[l['CURRENCY']]
            except:
                raise Exception('Unknown currency %s'%l['CURRENCY'])

        if ('STATUS' in l and
            l['STATUS'] not in [
                '\xee\xec\xe0',  # complete
                '\xe2\xee\xf8 \xe1\xe9\xf6\xe5\xf2', #end operation
                '\xe7\xec\xf7\xe9', # partial
                '-']):
            raise Exception('Unknown status %s'%l['STATUS'])

        if trntype in ['BUY', 'SELL']:
            if trntype == 'BUY' and units<=0: print 'Wrong units sign on buy'
            if trntype == 'SELL' and units>=0: print 'Wrong units sign on sell'
            if origunits and units!=origunits:
                logging.info('%s','Units differ from original units '+
                             l['UNITS']+' '+l['ORIGUNITS'])
            assert units
            assert unitprice
            cost=-net_total
            if trntype=='BUY':
                assert cost>0
            else:
                assert cost<0
        elif trntype == 'INT' or trntype == 'DIV':
            assert not units
            assert not origunits
            assert not unitprice
            interest=net_total
            assert interest>0
        elif trntype=='TAX':
            assert not units
            assert not unitprice
            assert not net_total
            assert not fees
            taxes=-origunits
            assert taxes>0
            total=-taxes
        elif trntype=='TAXRETURN':
            trntype='TAX'
            assert(units==0)
            assert(unitprice==0)
            assert(net_total==0)
            assert(fees==0)
            taxes=origunits
            assert taxes<0
            total=-taxes
        else:
            raise Exception('Bug - unknown trntype')

        diff=-units*unitprice+interest-fees-taxes-total
        if abs(diff)>0.5:
            if (version>0) and diff>0 and fees and not taxes:
                if trntype=='SELL':
                    logging.warning('%s',"%s price missmatch taking it as TAX on capital-gain %f=%.1f%%"%(stagvalue('DTSETTLE',dtsettle),diff,100.*diff/(-units*unitprice)))
                    taxes=diff
                elif trntype=='INT':
                    logging.warning('%s',"%s price missmatch taking it as TAX on interest %f=%.1f%%"%(stagvalue('DTSETTLE',dtsettle),diff,100.*diff/interest))
                    taxes=diff
                else:
                    logging.error('%s',"%s price missmatch %f"%
                                  (stagvalue('DTSETTLE',dtsettle),diff))
            else:
                logging.error('%s',"%s price missmatch %f"%
                              (stagvalue('DTSETTLE',dtsettle),diff))

        acctid=fltparams.get('ACCTID',[]) if fltparams else []
        if len(acctid)!=1 or not acctid[0]:
            logging.error("ACCTID must be given using the -x ACCTID=... argument")
            raise Exception()
        strn=[]
        strn.append(stagvalue('ACCTID',acctid[0],hashing=hashing))
        strn.append(n3secid('TASE',l['SECID']))
        strn.append(stagvalue('DTTRADE',dtsettle)) # we must have a DTTRADE
        strn.append(stagvalue('DTSETTLE',dtsettle))
        strn.append(stagvalue('tran',trntype))
        if units: strn.append(stagvalue('units',units))
        strn=" ".join(strn)
        print >>fout,stagvalue('FITID',hash(strn)),
        print >>fout,strn,

        print >>fout,stagvalue('DTASOF',dtasof),
        print >>fout,stagvalue('ACCTTYPE','INVESTMENT'),
        print >>fout,stagvalue('SECNAME',l['SECNAME'],language="hebrew"),
        print >>fout,stagvalue('TRNTYPE',trntype),
        if unitprice: print >>fout,stagvalue('UNITPRICE',unitprice),
        if total: print >>fout,stagvalue('TOTAL',total),
        if fees: print >>fout,stagvalue('FEES',fees),
        if taxes: print >>fout,stagvalue('TAXES',taxes),
        print >>fout,stagvalue('CURRENCY',currency),
        print >>fout,"];"
    print >>fout,"."
